package com.portal.dao;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import org.apache.log4j.Logger;

import com.portal.dto.AccesoDTO;
import com.portal.dto.UsuarioDTO;
//import com.portal.dto.ComboDTO;

import oracle.jdbc.OracleTypes;

import utiles.AppProperties;
import utiles.ConexionPool;
import utiles.utilitarios;

public class AccesoDAO {
	private static Logger log = Logger.getLogger(AccesoDAO.class);
	 String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	 String serverNombre = (AppProperties.getProperty("aplicacion", "SERVERNOM"));
	 String horaActual = utilitarios.getFechaHoraActual(); //utilitarios.getHoraActual(); 
	 
	 
		public List<UsuarioDTO> getLogon(String Usr, String Pass, String term, String ipv4) throws SQLException, IOException{
			
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	//	Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
			
	        
	        String UsuerLog ="";
	        
	        
	        List<UsuarioDTO> listUserPerf = new ArrayList<UsuarioDTO>();
	        try {
	            String query = "{ call "+pkgVar+".Pkg_tic_acceso.getLogon(?,?,?,?,?,?) }"; 	       
	            CallableStatement  stmt = conn.prepareCall(query);	
	            stmt.setString(1, Usr);	
		        stmt.setString(2, Pass);
		        stmt.setString(3, term);	
		        stmt.setString(4, ipv4);	
		        stmt.setString(5, serverNombre);
	            stmt.registerOutParameter(6, OracleTypes.CURSOR);
	            stmt.execute();
	            ResultSet rs = (ResultSet)stmt.getObject(6);
	            
	            if (rs.next()) {
	            	UsuarioDTO perfUSR = new UsuarioDTO();
	            	perfUSR.setIdUsuario(rs.getString(1));
	            	perfUSR.setNomUsuario(rs.getString(2));
	            	perfUSR.setIdSede(rs.getString(3));
	            	perfUSR.setNomSede(rs.getString(4));
	            	perfUSR.setSedeDire(rs.getString(5));
	            	perfUSR.setSedeFono(rs.getString(6));
	            	perfUSR.setSiglaArea(rs.getString(7));
	            	perfUSR.setNomArea(rs.getString(8));
	            	perfUSR.setIdArea(rs.getString(9));
	            	//System.out.println("area: "+rs.getString(9));
	            	perfUSR.setIdProf(rs.getString(10));
	            	perfUSR.setIdConexion(rs.getString(11));
	            	perfUSR.setIdUserStaff(rs.getString(12));
	            	perfUSR.setIdAreaStaff(rs.getString(13));
	            	perfUSR.setJefeArea(rs.getString(14));
	            	perfUSR.setIsRM(rs.getString(15));
	            	perfUSR.setIsDTQF(rs.getString(16));
	            	perfUSR.setEmail(rs.getString(18));
	            	listUserPerf.add(perfUSR);
	            	
	            	UsuerLog = horaActual+" Entra usuario: "+Usr+ " Nombre:"+rs.getString(2);
	            }else{
	            	UsuerLog = horaActual+" usuario y contrase�a incorrecto: "+Usr+ "-"+Pass;
            	
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error AccesoDAO.getLogon SQLException: " +ex.getMessage());
	        	System.out.println(horaActual+" "+ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	            System.out.println(UsuerLog);
	            log.info(UsuerLog);
	            
	        }
	        return listUserPerf;		
		}	 

		public List<AccesoDTO> getAcceso(String Usr) throws SQLException, IOException{
			
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    		
	        
	        List<AccesoDTO> listUserPerf = new ArrayList<AccesoDTO>();
	        try {
	            String query = "{ call "+pkgVar+".Pkg_per_acceso.getAccesoByIdUser(?,?) }"; 	       
	            CallableStatement  stmt = conn.prepareCall(query);	
	            stmt.setString(1, Usr);	
	            stmt.registerOutParameter(2, OracleTypes.CURSOR);
	            stmt.execute();
	            ResultSet rs = (ResultSet)stmt.getObject(2);
	            
	            while (rs.next()) {
	            	AccesoDTO perfUSR = new AccesoDTO();
	            	if (rs.getString(1)==null)
	            		perfUSR.setIdMatriz("");
	            	else
	            		perfUSR.setIdMatriz(rs.getString(1));
	            	perfUSR.setNomNegocio(rs.getString(2));
	            	perfUSR.setNomSistema(rs.getString(3));
	            	perfUSR.setNomModulo(rs.getString(4));
	            	perfUSR.setNomProceso(rs.getString(5));
	            	perfUSR.setIdNegocio(rs.getString(6));
	            	perfUSR.setIdSistema(rs.getString(7));
	            	perfUSR.setIdModulo(rs.getString(8));
	            	perfUSR.setIdProceso(rs.getString(9));
	            	listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error AccesoDAO.getAcceso SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}	 

		public List<AccesoDTO> setAcceso(String idCon) throws SQLException, IOException{
			
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	
	        
	        List<AccesoDTO> listUserPerf = new ArrayList<AccesoDTO>();
	        try {
	            String query = "{ call "+pkgVar+".Pkg_per_acceso.setAcceso(?,?) }"; 	       
	            CallableStatement  stmt = conn.prepareCall(query);	
	            stmt.setString(1, idCon);	
	            stmt.registerOutParameter(2, OracleTypes.CURSOR);
	            stmt.execute();
	            ResultSet rs = (ResultSet)stmt.getObject(2);
	            
	            while (rs.next()) {
	            	AccesoDTO perfUSR = new AccesoDTO();
	            	if (Integer.parseInt(rs.getString(1))==0)
	            		System.out.println(horaActual+" IdConexion:"+rs.getString(2)+" "+rs.getString(3)+ " "+rs.getString(4));
	            	listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error AccesoDAO.setAcceso SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}	 

		public List<AccesoDTO> getVersion() throws SQLException, IOException{

			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	
	        List<AccesoDTO> listUserPerf = new ArrayList<AccesoDTO>();
	        try {
	        	String query = "{ call "+pkgVar+".Pkg_per_acceso.getVersion(?) }"; 	       
	            CallableStatement  stmt = conn.prepareCall(query);	
	            stmt.registerOutParameter(1, OracleTypes.CURSOR);
	            stmt.execute();
	            ResultSet rs = (ResultSet)stmt.getObject(1);
	            while (rs.next()) {
	            	AccesoDTO perfUSR = new AccesoDTO();
	            	perfUSR.setIdMatriz(rs.getString(3));
	            	perfUSR.setIdModulo(rs.getString(4));
	            	perfUSR.setIdNegocio("0081");
	            	listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error AccesoDAO.getVersion SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//System.out.println("entro a version");
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}
		
		public List<AccesoDTO> setCambiarPass(String IdUsr, String PassA, String PassN) throws SQLException, IOException{
			
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    		
	        
	        List<AccesoDTO> listUserPerf = new ArrayList<AccesoDTO>();
	        try {
	            String query = "{ call "+pkgVar+".Pkg_tic_acceso.setCambiarPass(?,?,?,?) }"; 	       
	            CallableStatement  stmt = conn.prepareCall(query);
	            stmt.setString(1, IdUsr);
	            stmt.setString(2, PassA);
	            stmt.setString(3, PassN);
	            
	            stmt.registerOutParameter(4, OracleTypes.CURSOR);
	            stmt.execute();
	            ResultSet rs = (ResultSet)stmt.getObject(4);
	            
	            while (rs.next()) {
	            	AccesoDTO perfUSR = new AccesoDTO();
	            	perfUSR.setIdMatriz(rs.getString(1));
	            	perfUSR.setIdModulo(rs.getString(2));
	            	listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error AccesoDAO.getVersion SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}
		
	

		   public byte[] getFile(String idReg, String tabla) throws SQLException, IOException
		   {  
				//ConexionPool connDao = new ConexionPool();
			   Connection conn = ConexionPool.getConnectionPool();	
		    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
		    		
		       byte[] data =null;  
		       try {  
					String query = "{ call "+pkgVar+".Pkg_per_acceso.getFile(?,?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, idReg);
					stmt.setString(2, tabla);
					stmt.registerOutParameter(3, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(3);
					while (rs.next()) {
						Blob blob=rs.getBlob(1);
						InputStream bodyOut = blob.getBinaryStream();
						data = new byte[ (int) blob.length()]; 
						bodyOut.read(data);
					}
					rs.close();
					stmt.close();					
		       } catch (FileNotFoundException e) {  
		    	   System.out.println(utilitarios.getHoraActual()+" Error file not found");
		           // TODO  
		       } catch (IOException e) {  
		    	   System.out.println(utilitarios.getHoraActual()+" Error IO Exception");
		    	   //connDao.desconectaPool(conn);
		           // TODO  

		       } finally {
		    		//ConexionPool.desconectaPool(conn);
					conn.close();
					conn = null;
		       }

		    	   return data;  


		   }  
		   

			//
		   
			public static int sendMailLiga(String to, String cc, String body, String setSubject, String confEmail[], String tipoTexto)
			{
					int varEstado;
			   		try
			    		{
			   			varEstado=1;
			   			
			   			Properties props = new Properties();
			   			props.clear();
			   			List<String> param=Arrays.asList(confEmail);
			      		//props.put("mail.smtp.host", smtpServer);
						String smtpServer = "smtp.liche.cl"; //param.get(0);//(AppProperties.getProperty("aplicacion", "smtpServer"));
						String smtpAuth   = "FALSE"; //param.get(1);//(AppProperties.getProperty("aplicacion", "smtpAuth"));
						String smtpUser   = param.get(2);//(AppProperties.getProperty("aplicacion", "smtpUser"));
						String from       = param.get(2);
						String smtpPass   = "";//(AppProperties.getProperty("aplicacion", "smtpPass"));
						String smtpPort   = "25"; 	 //param.get(4);//(AppProperties.getProperty("aplicacion", "smtpPort"));
						String smtpStls   = "FALSE"; //param.get(5);//(AppProperties.getProperty("aplicacion", "smtpStls"));
						
						
			   			props.setProperty("mail.smtp.host", smtpServer);
			   			props.setProperty("mail.smtp.mail.sender",from); 
			   			props.setProperty("mail.smtp.user", from);
			   			props.setProperty("mail.smtp.auth", smtpAuth);
			      		props.setProperty("mail.smtp.port", smtpPort);
			      		props.setProperty("mail.smtp.starttls.enable", smtpStls);
			      		Session session = Session.getDefaultInstance(props);
			      		
			      		/*
			      		session.setDebug(true);
			      		System.out.println("**********************************");
			      		System.out.println("**********************************");
			      		System.out.println(props);
			      		System.out.println("**********************************");
			      		System.out.println("**********************************");

			      		
			     
			      		System.out.println("smtpServer:"+smtpServer);
			      		System.out.println("smtpUser:"+smtpUser);
			      		System.out.println("smtpPass:"+smtpPass);
			      		System.out.println("smtpAuth:"+smtpAuth);
			      		*/
			      		MimeMessage message = new MimeMessage(session);
			      		message.setFrom(new InternetAddress(from));
			      		message.setSender(new InternetAddress(from));
			      		
			      		
			      		message.addRecipients(Message.RecipientType.TO, to);
			      		if (cc!=null) message.addRecipient(Message.RecipientType.CC, new InternetAddress(cc));
			      		message.setSubject(setSubject);
			      		if (tipoTexto=="html")	message.setText(body, "ISO-8859-1", "html");
			      		else   			message.setText(body);
			      		Transport t = session.getTransport("smtp");
			      		t.connect(smtpUser,smtpPass);
			      		t.sendMessage(message,message.getAllRecipients());
			      		t.close();
			      		/*
			      		props.clear();
			      		props=null;
			      		message=null;
			      		session=null;
			      		*/
			      		
			      		
			      		//System.out.println(utilitarios.getHoraActual()+" Email enviado de:"+from+" Para:"+to+";"+cc);
			      		//log.error(utilitarios.getHoraActual()+" Email enviado de:"+from+" Para:"+to+";"+cc);
			    	}catch (MessagingException mex)  {
			    		System.out.println("**************************************************");
			    		System.out.println("** INICIO ERROR AccesoDAO.sendMailLiga ***********");
			    		//System.out.println(utilitarios.getHoraActual()+" :: "+mex.getMessage());
			    		System.out.println("**************************************************");
			    		//mex.printStackTrace();

			    		/*Exception ex = null;
			    		  if ((ex = mex.getNextException()) != null) {
					    		//ex.printStackTrace();

			    		  }
			    		  */
			      		//ex.printStackTrace();
			      		varEstado=0;
			      		log.error(utilitarios.getHoraActual()+"*******************************************");
			      		log.error(utilitarios.getHoraActual()+"*** INICIO ERROR AccesoDAO.sendMailLiga  **");
			      		log.error(utilitarios.getHoraActual()+mex.getMessage());
			      		log.error(utilitarios.getHoraActual()+"*******************************************");
			      		//log.error(utilitarios.getHoraActual()+" Error TicketDAO.sendMailLiga en envío de mensaje,"+ mex.getMessage() );
			    	} finally {
			    		
			    	}
			    	return varEstado;
			}		   
		   
		   public static void main(String[] args) throws SQLException, IOException{
			   AccesoDAO login = new AccesoDAO();
			   login.getLogon("lgutierrez", "a", "term", "ipv4");
		   }
		   
		   
		   
		   public List<AccesoDTO> getHistorialPortal() throws SQLException, IOException{
				
				//ConexionPool connDao = new ConexionPool();
				Connection conn = ConexionPool.getConnectionPool();	
		    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
		    	
		        
		        List<AccesoDTO> listHistoral = new ArrayList<AccesoDTO>();
		        try {
		            String query = "{ call "+pkgVar+".Pkg_per_acceso.getHistorialPortal(?) }"; 	       
		            CallableStatement  stmt = conn.prepareCall(query);	
		            stmt.registerOutParameter(1, OracleTypes.CURSOR);
		            stmt.execute();
		            ResultSet rs = (ResultSet)stmt.getObject(1);
		            
		            while (rs.next()) {
		            	AccesoDTO perfUSR = new AccesoDTO();
		            	perfUSR.setNomSistema(rs.getString(1));
		            		
		            	listHistoral.add(perfUSR);
		            }
		            rs.close();
		            stmt.close();
		        } catch (SQLException ex) {  
		           log.error("Error AccesoDAO.setAcceso SQLException: " +ex.getMessage());
		        	System.out.println(ex.getMessage());
		        } finally {
		        	//ConexionPool.desconectaPool(conn);
					conn.close();
					conn = null;
		        }
		        return listHistoral;		
			}	 
		   
		   
		   
		   
		   

}
